from sqlalchemy.orm import mapper, relation
from sqlalchemy import Table, ForeignKey, Column , SmallInteger, Float,Sequence,desc
from sqlalchemy.types import Integer, Unicode ,DateTime ,Binary, UnicodeText,Boolean
from sqlalchemy.orm import relation, backref,eagerload
from pydive.model import DeclarativeBase, metadata, DBSession
from pydive.model.auth import User
from datetime import datetime

#from . import User


news_tags = Table('news_tags', metadata,
     Column('news_id', Integer, ForeignKey('news.id')),
     Column('tag_id', Integer, ForeignKey('newsTag.id'))
)
   
class News(DeclarativeBase):
    __tablename__ = 'news'
     
    id = Column(Integer, autoincrement=True, primary_key=True)
    date = Column(DateTime,nullable=False,default=datetime.now)
    title = Column(Unicode(255))
    short_text = Column(UnicodeText)
    text = Column(UnicodeText)
    frontPage = Column(Boolean, default=False)
    author_id = Column(Integer, ForeignKey(User.__tablename__+'.user_id'))
    author= relation(User, backref=backref('news', order_by=date))
    # many to many News<->NewsTag
    keywords = relation('NewsTag', secondary=news_tags, backref='news')

    def div_short_text(self):
        return '<div>'+self.short_text+'</div>'
    def div_text(self):
        return '<div>'+self.text+'</div>'
    def first(self,num,frontpage=True):
        return DBSession.query(News).filter(News.author_id!=None and News.frontPage == frontpage).options(eagerload('author')).order_by(desc(News.date)).limit(5).all()

class NewsTag(DeclarativeBase):
    __tablename__ = 'newsTag'
    id = Column(Integer, autoincrement=True, primary_key=True)
    tag = Column(Unicode(50), unique=True, nullable=False)